package mk.company.fin.service;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceContextType;
import javax.persistence.TypedQuery;

import mk.company.fin.model.Model;
import mk.company.fin.reports.model.KontoStatusReport;
import mk.company.fin.reports.model.SyntheticCardReport;
import mk.company.fin.reports.model.WarrantEntryReport;
import mk.company.fin.utils.StringEntry;

import org.apache.commons.collections.CollectionUtils;
import org.hibernate.CacheMode;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * Data (DB) manipulation service: insert, update, delete and search functionality.
 * 
 * @author vlado.gjorgjiev
 */
@Service
@Transactional
public class ModelService {
	
	private static final String JOIN = " JOIN ";
	private static final String WHERE = " WHERE ";
	private static final String AND = " AND ";
	private static final String BASEALIAS = " base ";
	private static final String EQUALSIGN = " = ";
	private static final String SELECTFROM = "SELECT" + BASEALIAS + "FROM ";

	@PersistenceContext(type = PersistenceContextType.EXTENDED)
	private EntityManager entityManager;
	
	
	/* SEARCH MANIPULATION */
	
	/**
	 * Searches the given type to find instance with the given (persisted) id.
	 * @param classType the class type
	 * @param id id
	 * @return the instance of null (if can't find)
	 */
	public <T extends Model> T findById(Class<T> classType, final Long id) {
		return entityManager.find(classType, id);
	}
	
	/**
	 * @param classType the class type
	 * @return all the instances from the given type
	 */
	public <T extends Model> List<T> findAll(Class<T> classType) {
		StringBuilder builder = new StringBuilder();
		builder.append(SELECTFROM);
		builder.append(classType.getName());
		builder.append(BASEALIAS);
		return entityManager.createQuery(builder.toString(), classType).getResultList();
	}
	
	/**
	 * Returns instances from the given type by the following criteria:
	 * <ul>
	 * <li>creates JOINs from the joinClasses parameter (key = property path, value = table alias)</li>
	 * <li>creates WHERE conditions from the properties (key = value path, value = real value)</li>
	 * </ul>
	 * NOTE: the given type alias is always: base
	 * @param classType the class type
	 * @param joinClasses the JOINs
	 * @param properties the WHERE conditions
	 * @return the result list
	 */
	public <T extends Model> List<T> findByProperties(Class<T> classType, 
			List<StringEntry> joinClasses, 
			List<StringEntry> properties) {
		
		// form the query
		StringBuilder builder = new StringBuilder();
		builder.append(SELECTFROM);
		builder.append(classType.getName());
		builder.append(BASEALIAS);
		
		// join
		if (CollectionUtils.isNotEmpty(joinClasses)) {
			for (StringEntry entry : joinClasses) {
				builder.append(JOIN);
				builder.append(entry.getKey());
				builder.append(" ");
				builder.append(entry.getValue());
			}
		}
		
		// where
		if (CollectionUtils.isNotEmpty(properties)) {
			builder.append(WHERE);
			for (StringEntry entry : properties) {
				builder.append(entry.getKey());
				builder.append(EQUALSIGN);
				builder.append(entry.getValue());
				builder.append(AND);
			}
			builder.delete(builder.length() - AND.length(), builder.length());
		}
		
		// search and result
		TypedQuery<T> typedQuery = entityManager.createQuery(builder.toString(), classType);
		return typedQuery.getResultList();
	}

	
	
	/* DATA CORE MANIPULATION */

	public void save(final Model entity) {
		System.out.println(entity);
		entityManager.persist(entity);
	}

	public void update(final Model entity) {
		entityManager.merge(entity);
	}

	public void delete(final Model entity) {
		entityManager.remove(entity);
	}

	public void clear(final Model entity) {
		entityManager.detach(entity);
	}
	public void refresh(final Model entity) {
		entityManager.refresh(entity);
	}

	public List<WarrantEntryReport> getWarrantReport(Date startDate,Date endDate){
		String queryString="";
		String startDateQuery="";
		String endDateQuery="";
		SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
		if (startDate!=null){
			startDateQuery=" AND date >= '"+simpleDateFormat.format(startDate)+"' ";

		}
		if (endDate!=null){
			endDateQuery=" AND date <= '"+simpleDateFormat.format(endDate)+"' ";
		}

		String WarrantEntryClassName="fin_nalogentry";
		String WarrantClassName="fin_nalog";
		queryString="SELECT IFNULL(sumaDolzi,0) as sumaDolzi,IFNULL(sumaPobaruva,0) as sumaPobaruva, t1.date as date,t1.number as number FROM (SELECT SUM(amount) as sumaPobaruva,type,date,number FROM "+WarrantClassName+" t1,"+WarrantEntryClassName+" t2 WHERE t1.id_nalog=t2.id_nalog"+startDateQuery+" "+endDateQuery+" AND type='pobaruva' GROUP BY date,number ) t1 LEFT OUTER JOIN "+
	    "(SELECT SUM(amount) as sumaDolzi,type,date,number FROM "+WarrantClassName+" t1, "+WarrantEntryClassName+" t2 WHERE t1.id_nalog=t2.id_nalog AND type='dolzi' "+startDateQuery+" "+endDateQuery+" GROUP BY date,number,type) t2 on t1.date=t2.date AND t1.number=t2.number UNION "+
	    "SELECT IFNULL(sumaDolzi,0)as sumaDolzi,IFNULL(sumaPobaruva,0) as sumaPobaruva, t2.date as date,t2.number as number FROM (SELECT SUM(amount) as sumaPobaruva,type,date,number FROM "+WarrantClassName+" t1,"+WarrantEntryClassName+" t2 WHERE t1.id_nalog=t2.id_nalog"+startDateQuery+" "+endDateQuery+" AND type='pobaruva' GROUP BY date,number ) t1 RIGHT OUTER JOIN "+
	    "(SELECT SUM(amount) as sumaDolzi,type,date,number FROM "+WarrantClassName+" t1, "+WarrantEntryClassName+" t2 WHERE t1.id_nalog=t2.id_nalog AND type='dolzi' "+startDateQuery+" "+endDateQuery+" GROUP BY date,number,type) t2 on t1.date=t2.date AND t1.number=t2.number";
		
		Session session = (Session)entityManager.getDelegate();
		session.setCacheMode(CacheMode.IGNORE);
		session.clear();
		SQLQuery query=session.createSQLQuery(queryString).addEntity(WarrantEntryReport.class);
		return query.list();
	}
	
	public List<KontoStatusReport> getKontoStatusReport(Date startDate,Date endDate, String kontoClass, String kontoSynthetic){
		String kontoClassQueryString="";
		if (!kontoClass.equals("")){
			kontoClassQueryString=" AND kc.value='"+kontoClass+"' ";
		}
		String kontoSyntheticQueryString="";
		if (!kontoSynthetic.equals("")){
			kontoSyntheticQueryString=" AND kc.value='"+kontoSynthetic.charAt(0)+"' AND kg.value='"+kontoSynthetic.charAt(1)+"' AND ks.value='"+kontoSynthetic.charAt(2)+"' ";
		}
		DateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
		String startDateStartQueryString=" AND n.date < '"+dateFormat.format(startDate)+"' ";
		String startDateCurrentQueryString=" AND n.date >= '"+dateFormat.format(startDate)+"' ";
		String endDateQueryString=" AND n.date <= '"+dateFormat.format(endDate)+"' ";
		String queryString="SELECT CONCAT(kc.value,kg.value,ks.value,ko.value) as konto,data.komintent as komintentCode,km.name as komintentName,os.description as MTR,data.pdolzi as startDebt,data.ppobaruva as startDemand,data.mdolzi as currentDebt,data.mpobaruva as currentDemand FROM"+
					" ("+
					" SELECT momentalna.konto,momentalna.komintent,momentalna.sector,IFNULL(pocetna.dolzi,0) as pdolzi,IFNULL(pocetna.pobaruva,0) as ppobaruva,momentalna.dolzi as mdolzi,momentalna.pobaruva as mpobaruva FROM "+
					" (SELECT dolzi.konto,dolzi.komintent,dolzi.sector,dolzi.suma as dolzi,IFNULL(pobaruva.suma,0) as pobaruva FROM "+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+ 
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector "+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) dolzi LEFT" +
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n" +
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) pobaruva" +
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector"+
					" UNION"+
					" SELECT pobaruva.konto,pobaruva.komintent,pobaruva.sector,IFNULL(dolzi.suma,0) as dolzi,pobaruva.suma as pobaruva FROM"+ 
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) dolzi RIGHT" +
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) pobaruva"+ 
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector) pocetna"+ 
					" RIGHT OUTER JOIN"+
					" (SELECT dolzi.konto,dolzi.komintent,dolzi.sector,dolzi.suma as dolzi,IFNULL(pobaruva.suma,0) as pobaruva FROM"+ 
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+ 
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) dolzi LEFT "+
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+ 
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) pobaruva "+
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector"+
					" UNION"+
					" SELECT pobaruva.konto,pobaruva.komintent,pobaruva.sector,IFNULL(dolzi.suma,0) as dolzi,pobaruva.suma as pobaruva FROM "+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) dolzi RIGHT"+ 
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) pobaruva"+ 
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector) momentalna"+
					" ON pocetna.konto=momentalna.konto AND pocetna.komintent=momentalna.komintent AND pocetna.sector=momentalna.sector"+
					" ) data, fin_kontoclass kc,fin_kontogroup kg,fin_kontosynthetic ks, fin_konto ko, fin_komintent km,fin_organizationsector os WHERE"+
					" kc.id_konto_class=kg.id_konto_class AND kg.id_konto_group=ks.id_konto_group"+
					" AND ks.id_konto_synthetic=ko.id_konto_synthetic AND ko.id_konto_analytic=data.konto"+ 
					" AND km.code=data.komintent AND os.id_organization_sector=data.sector"+kontoClassQueryString+kontoSyntheticQueryString+
					" UNION "+
					"SELECT CONCAT(kc.value,kg.value,ks.value,ko.value) as konto,data.komintent as komintentCode,km.name as komintentName,os.description as MTR,data.pdolzi as startDebt,data.ppobaruva as startDemand,data.mdolzi as currentDebt,data.mpobaruva as currentDemand FROM"+
					" ("+
					" SELECT pocetna.konto,pocetna.komintent,pocetna.sector,pocetna.dolzi as pdolzi,pocetna.pobaruva as ppobaruva,IFNULL(momentalna.dolzi,0) as mdolzi,IFNULL(momentalna.pobaruva,0) as mpobaruva FROM "+
					" (SELECT dolzi.konto,dolzi.komintent,dolzi.sector,dolzi.suma as dolzi,IFNULL(pobaruva.suma,0) as pobaruva FROM "+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+ 
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector "+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) dolzi LEFT" +
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n" +
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) pobaruva" +
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector"+
					" UNION"+
					" SELECT pobaruva.konto,pobaruva.komintent,pobaruva.sector,IFNULL(dolzi.suma,0) as dolzi,pobaruva.suma as pobaruva FROM"+ 
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) dolzi RIGHT" +
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateStartQueryString+
					" GROUP BY konto,komintent,sector) pobaruva"+ 
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector) pocetna"+ 
					" LEFT OUTER JOIN"+
					" (SELECT dolzi.konto,dolzi.komintent,dolzi.sector,dolzi.suma as dolzi,IFNULL(pobaruva.suma,0) as pobaruva FROM"+ 
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+ 
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) dolzi LEFT "+
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+ 
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) pobaruva "+
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector"+
					" UNION "+
					" SELECT pobaruva.konto,pobaruva.komintent,pobaruva.sector,IFNULL(dolzi.suma,0) as dolzi,pobaruva.suma as pobaruva FROM "+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='DOLZI'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) dolzi RIGHT"+ 
					" OUTER JOIN"+
					" (SELECT ne.id_konto as konto,km.code as komintent,os.id_organization_sector as sector,SUM(ne.amount) as suma FROM fin_konto k,fin_nalogentry ne,fin_organizationsector os,fin_komintent km ,fin_nalog n"+
					" WHERE k.id_konto_analytic=ne.id_konto "+
					" AND km.id_komintent=ne.id_komintent "+
					" AND ne.id_organization_sector=os.id_organization_sector"+
					" AND ne.type='POBARUVA'"+
					" AND ne.id_nalog=n.id_nalog"+startDateCurrentQueryString+endDateQueryString+
					" GROUP BY konto,komintent,sector) pobaruva"+ 
					" ON dolzi.konto=pobaruva.konto AND dolzi.komintent=pobaruva.komintent AND dolzi.sector=pobaruva.sector) momentalna"+
					" ON pocetna.konto=momentalna.konto AND pocetna.komintent=momentalna.komintent AND pocetna.sector=momentalna.sector"+
					" ) data, fin_kontoclass kc,fin_kontogroup kg,fin_kontosynthetic ks, fin_konto ko, fin_komintent km,fin_organizationsector os WHERE"+
					" kc.id_konto_class=kg.id_konto_class AND kg.id_konto_group=ks.id_konto_group"+
					" AND ks.id_konto_synthetic=ko.id_konto_synthetic AND ko.id_konto_analytic=data.konto";
					Session session = (Session)entityManager.getDelegate();
					session.setCacheMode(CacheMode.IGNORE);
					session.clear();
					SQLQuery query=session.createSQLQuery(queryString).addEntity(KontoStatusReport.class);
					return query.list();
	}
	
	public List<SyntheticCardReport> getSyntheticCardReport(Date startDate,Date endDate, String kontoSynthetic){
		DateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
		String kontoSyntheticQueryString="";
		if (!kontoSynthetic.equals("")){
			kontoSyntheticQueryString=" AND kc.value='"+kontoSynthetic.charAt(0)+"' AND kg.value='"+kontoSynthetic.charAt(1)+"' AND ks.value='"+kontoSynthetic.charAt(2)+"' ";
		}
		String startDateCurrentQueryString="";
		if (startDate!=null)
			startDateCurrentQueryString=" AND n.date >= '"+dateFormat.format(startDate)+"' ";
		String endDateQueryString="";
		if (endDate!=null)
			endDateQueryString=" AND n.date <= '"+dateFormat.format(endDate)+"' ";
		
		String queryString=""+
		" SELECT dolzi.date as warrantDate,dolzi.number as warrantNumber,dolzi.amount as debt,IFNULL(pobaruva.amount,0) as demand FROM "+
		" (SELECT n.date,n.number,SUM(ne.amount) as amount FROM fin_nalog n,fin_nalogentry ne, fin_kontoclass kc,fin_kontogroup kg,fin_kontosynthetic ks, fin_konto ko"+
		" WHERE n.id_nalog=ne.id_nalog AND ne.type='DOLZI' AND kc.id_konto_class=kg.id_konto_class AND kg.id_konto_group=ks.id_konto_group "+
		" AND ks.id_konto_synthetic=ko.id_konto_synthetic AND ko.id_konto_analytic=ne.id_konto "+kontoSyntheticQueryString+startDateCurrentQueryString+endDateQueryString+" GROUP BY date,number,type) dolzi"+
		" LEFT OUTER JOIN "+
		" (SELECT n.date,n.number,SUM(ne.amount) as amount FROM fin_nalog n,fin_nalogentry ne, fin_kontoclass kc,fin_kontogroup kg,fin_kontosynthetic ks, fin_konto ko"+
		" WHERE n.id_nalog=ne.id_nalog AND ne.type='POBARUVA' AND kc.id_konto_class=kg.id_konto_class AND kg.id_konto_group=ks.id_konto_group"+
		" AND ks.id_konto_synthetic=ko.id_konto_synthetic AND ko.id_konto_analytic=ne.id_konto "+kontoSyntheticQueryString+startDateCurrentQueryString+endDateQueryString+" GROUP BY date,number,type) pobaruva"+
		" ON dolzi.date=pobaruva.date AND dolzi.number=pobaruva.number"+
		" UNION "+
		" (SELECT dolzi.date as warrantDate ,dolzi.number as warrantNumber,IFNULL(dolzi.amount,0) as debt,pobaruva.amount as demand FROM "+
		" (SELECT n.date,n.number,SUM(ne.amount) as amount FROM fin_nalog n,fin_nalogentry ne,fin_kontoclass kc,fin_kontogroup kg,fin_kontosynthetic ks , fin_konto ko"+
		" WHERE n.id_nalog=ne.id_nalog AND ne.type='DOLZI' AND kc.id_konto_class=kg.id_konto_class AND kg.id_konto_group=ks.id_konto_group"+
		" AND ks.id_konto_synthetic=ko.id_konto_synthetic AND ko.id_konto_analytic=ne.id_konto "+kontoSyntheticQueryString+startDateCurrentQueryString+endDateQueryString+" GROUP BY date,number,type) dolzi"+
		" RIGHT OUTER JOIN "+
		" (SELECT n.date,n.number,SUM(ne.amount) as amount FROM fin_nalog n,fin_nalogentry ne,fin_kontoclass kc,fin_kontogroup kg,fin_kontosynthetic ks , fin_konto ko"+
		" WHERE n.id_nalog=ne.id_nalog AND ne.type='POBARUVA' AND kc.id_konto_class=kg.id_konto_class AND kg.id_konto_group=ks.id_konto_group"+
		" AND ks.id_konto_synthetic=ko.id_konto_synthetic AND ko.id_konto_analytic=ne.id_konto "+kontoSyntheticQueryString+startDateCurrentQueryString+endDateQueryString+" GROUP BY date,number,type) pobaruva"+
		" ON dolzi.date=pobaruva.date AND dolzi.number=pobaruva.number )";
		
		Session session = (Session)entityManager.getDelegate();
		session.setCacheMode(CacheMode.IGNORE);
		session.clear();
		SQLQuery query=session.createSQLQuery(queryString).addEntity(SyntheticCardReport.class);
		return query.list();
	}
		
}
